const { search } = require("./api/user");

// sql语句
var sql = {
    // 登录
    loginInfo: {
        login: 'select * from login_info where userId = ? and passWord = ?',
    },
    // 学生信息
    studentInfo: {
        // 查询+分页
        queryStudentList:'select * from student_info',
        // 查询宿舍楼 
        queryBuildNo:'select buildNo from apartment_info group by buildNo',
        // 删除学生信息
        //重置密码
        resetPassword:'UPDATE login_info SET `passWord` = (SELECT RIGHT(idCard,6) FROM student_info WHERE student_info.studentId = ? )',
        // 将原数据备份到表change_record中
        record:'INSERT INTO change_record SELECT studentId,	studentName,sex,idCard,department,professional,buildNo,dormNo,bedNo,dormType,address,counselor,coPhone FROM student_info WHERE	studentId = ?',
        // 更新编辑数据
        // 床位空余
        nolive:'UPDATE apartment_info SET isLive = 0 WHERE buildNo in (SELECT buildNo from student_info WHERE studentId = ?) AND dormNo in (SELECT dormNo from student_info WHERE studentId = ?) and bedNo in (SELECT bedNo from student_info WHERE studentId = ?)',
        // 更新住宿信息
        update:'UPDATE student_info SET isNull = ?, buildNo=?, dormNo=?,bedNo=?,dormType=(SELECT dormType FROM apartment_info WHERE buildNo = ? and dormNo =? and bedNo=?)WHERE studentId = ?',
        // 床位被占
        havelive:'UPDATE apartment_info SET isLive = 1 WHERE buildNo in (SELECT buildNo from student_info WHERE studentId = ?) AND dormNo in (SELECT dormNo from student_info WHERE studentId = ?) and bedNo in (SELECT bedNo from student_info WHERE studentId = ?)',
        // 新增住宿信息
        addStudentDorm:'INSERT INTO student_info (studentId, studentName, sex, idCard, department, professional, buildNo, dormNo, bedNo, dormType, address, counselor, coPhone) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)',
        // 新增用户住宿信息
        // 查询选中宿舍楼的宿舍号
        addDormNo:'SELECT dormNo from apartment_info where buildNo = ? GROUP BY dormNo',
        addBedNo:'SELECT bedNo FROM apartment_info where buildNo = ? and dormNo= ? and isLive = ?',
        newsList:'select * from news order by date desc limit 5 ',
        addnewsList:'INSERT INTO news (title, date, link) VALUES (?,?,?)',

    },
    //违纪信息
    violation:{
        queryAllVioList:"select * from violation",
        addVio:"INSERT INTO violation (date, studentId, studentName, buildNo, dormNo, bedNo, professional, counselor,vioType,vioThing,vioNum,commitMan,remarks) VALUES (?, ?, ?, ?,?, ?, ?, ?,?,?,?,?,?)",
        vioStudent:"SELECT * from student_info where buildNo = ? and dormNo = ? and bedNo = ?",
        vioThingsList:"select * from vio_things",
        editVio:'update violation set remarks=?, isTrue = ? where date =? and studentId =? and vioType  = ?',
        samedorm:'select * from violation v where studentId in(select studentId from student_info where buildNo in(select buildNo from student_info where studentId =?) and dormNo in (select dormNo from student_info where studentId =?))'
    
    },
    // 宿舍详情
    dormDetail:{
        dormDetail:"select * from (SELECT LEFT(dormNo,1) as floor, dormNo,capacity,SUM(isLive>0) as countLive from apartment_info WHERE buildNo = ? GROUP BY dormNo,capacity ORDER BY dormNo asc) as a",
        floorNum:"SELECT LEFT(dormNo,1) as floor from apartment_info WHERE buildNo = ?  GROUP BY dormNo,capacity ORDER BY dormNo desc LIMIT 1",
        countBuild:"SELECT b.buildNo, b.sex, COUNT(b.buildNo) as totalDorm FROM (SELECT a.buildNo,a.dormNo,a.sex,COUNT(a.dormNo) as totalDorm from  (SELECT buildNo,dormNo,sex from apartment_info GROUP BY buildNo, dormNo, sex ) as a GROUP BY a.buildNo, a.dormNo, a.sex) as b GROUP BY b.buildNo, b.sex",
        dormLiveStatus:"SELECT buildNo, COUNT(buildNo) as DormStatusNum from (SELECT buildNo, dormNo, capacity, SUM(isLive>0) as liveNum  from apartment_info where buildNo in (SELECT buildNo from apartment_info GROUP BY buildNo)GROUP BY capacity, dormNo, buildNo) a  ",
        // 每个宿舍楼的总数
        // delDormNo:"delete from apartment_info where dormNo in (?)"
        // 多床位改为少床位
        reduceDormBed:"delete from apartment_info ai where dormNo = ? and  bedNo > ?",
        // 多床位改为少床位
        increaseDormBed:"",
        //更新数据
        updatedorminfo:"update apartment_info set capacity=? where buildNo =? and dormNo =?"
    },
    perInfo:{
        getPerInfo:"SELECT * from login_info ",
        addPerInfo:'INSERT INTO login_info (userId, userName, permissions,passWord) VALUES (?,?,?,"123456")',
        reducePer:"delete from login_info where userId = ? and permissions = ?",
        updatePer:"update login_info set permissions=? where userId = ?",
        updatePerPw:"update login_info set passWord=? where userId = ?"


    },
    dataSummary:{
        provice:"SELECT left(address,2) as name, count(*) as value FROM student_info group by name",
        vioPie:"select vioThing as name,SUM(vioNum) as value  from violation where vioType = '2' group by (vioThing)",
        nightPie:"select buildNo as name,count(*) as value  from violation where vioType = '1' group by (buildNo)",
        zhu:"select buildNo ,count(buildNo) as studentNum from apartment_info where isLive = '1' group by buildNo ",
    
    }
}
    
module.exports = sql;